﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for PersonDBConnection
/// </summary>
public class PersonDBConnection
{
    OleDbConnection conn;
    OleDbCommand cmd;
    OleDbDataAdapter datatAdapter;
    OleDbDataReader dataReader;
    public String connString = "";

	public PersonDBConnection()
    {
        conn = new OleDbConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;
        //cmd = new OleDbCommand();
	}


    public Person getPersonByIDno(String person_ID_no)
    {
        connString = "SELECT * FROM Person WHERE ID_No = @Person_ID";
        cmd = conn.CreateCommand();
        cmd.CommandText = connString;

        conn.Open();

        OleDbParameter computerIDParam = new OleDbParameter();
        computerIDParam.ParameterName = "@Person_ID";
        computerIDParam.OleDbType = OleDbType.VarChar;
        computerIDParam.Size = 13;
        computerIDParam.Direction = ParameterDirection.Input;
        computerIDParam.Value = person_ID_no;

        cmd.Parameters.Add(computerIDParam);

        //myOleDbCommand.Connection.Open();
        dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        Person person = new Person();
        while (dataReader.Read())
        {
            person.person_ID_no = dataReader["ID_No"].ToString();
            person.firstname = dataReader["Firstname"].ToString();
            person.surname = dataReader["Surname"].ToString();
            person.cellphone = dataReader["Telephone"].ToString();
            person.telephone = dataReader["CellPhone"].ToString();
        }

        closeObjects();

        return person;
    }

 
    public DataSet getPersonsNotAuthorizedDataSet(int computerID)
    {
        DataSet ds = new DataSet();
        connString = "SELECT * FROM Person WHERE ID_No NOT IN (SELECT Asset_Allocation.ID_No FROM ((Hardware INNER JOIN Asset_Allocation ON Hardware.Hardware_ID = Asset_Allocation.Hardware_ID) INNER JOIN Computers ON Hardware.Hardware_ID = Computers.Hardware_ID) WHERE Computers.Computer_ID = "+computerID+");";

        cmd = conn.CreateCommand();

        conn.Open();
        OleDbDataAdapter da = new OleDbDataAdapter(connString, conn);
        da.Fill(ds, "PersonTable");

        closeObjects();

        return ds;
    }

    public void update(Person person)
    {
        String connStringUpdate = "UPDATE Person SET CellPhone = '" + person.cellphone + "', Firstname = '" + person.firstname + "', Surname = '" + person.surname + "', Telephone = '" + person.telephone + "' WHERE ID_No = '" + person.person_ID_no + "';";

        cmd = new OleDbCommand(connStringUpdate, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        closeObjects();
    }

    public void delete(Person person)
    {

        String connString = "DELETE FROM Person WHERE ID_No = '" + person.person_ID_no+ "';";

        cmd = new OleDbCommand(connString, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        closeObjects();
    }

    public void closeObjects()
    {
        if (conn.State.ToString().Equals("Open"))
        {
            conn.Close();
        }
        if (cmd.Connection.State.ToString().Equals("Open"))
        {
            cmd.Connection.Close();
        }
    }
    public void insert(Person person)
    {
        String connString = "INSERT INTO Person (ID_No,Firstname,Surname,Telephone,CellPhone) VALUES ('" + person.person_ID_no + "','" + person.firstname + "','" + person.surname + "','" + person.telephone + "','" + person.cellphone + "');";

        cmd = new OleDbCommand(connString, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        closeObjects();

    }
    public void insetAssetAllocation(String id_No, int hardwareID)
    {
        String connStringUpdate = "INSERT INTO Asset_Allocation (ID_No, Hardware_ID) VALUES ('" + id_No + "'," + hardwareID + ");";

        cmd = new OleDbCommand(connStringUpdate, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        closeObjects();

    }


}